<?php
class m_laporan extends CI_Model {
	
	function _list_proyek($search){
		$sql = "SELECT *
            FROM m_proyek
            LEFT JOIN m_cabang ON cabang_id = m_cabang_id  ";
            if ($search != ""){
                $sql .= " WHERE 0=1 ";
                $sql .= " OR proyek_kode like '%".$search."%' ";
                $sql .= " OR proyek_nama like '%".$search."%' ";
                $sql .= " OR proyek_pm like '%".$search."%' ";
                $sql .= " OR proyek_nk like '%".$search."%' ";
                $sql .= " OR proyek_nkppn like '%".$search."%' ";
                $sql .= " OR proyek_mulai like '%".$search."%' ";
                $sql .= " OR proyek_p1 like '%".$search."%' ";
                $sql .= " OR proyek_p2 like '%".$search."%' "; 
            }
            $sql .= "
            ORDER BY m_cabang_id";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function _list_mandor($search){
		$sql = "SELECT * FROM m_mandor LEFT JOIN m_keuangan ON m_keuangan_id = keuangan_id
         LEFT JOIN m_pendanaan ON m_pendanaan_id = pendanaan_id";
        if ($search != ""){
            $sql .= " WHERE 0=1 ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR mandor_status like '%".$search."%' ";
        }
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function _mon_mandor($search){
		$sql = "SELECT * FROM t_monitor
        LEFT JOIN m_mandor ON m_mandor_id = mandor_id
        LEFT JOIN m_proyek ON m_proyek_id = proyek_id
        LEFT JOIN m_cabang ON m_cabang_id = cabang_id
        LEFT JOIN m_pekerjaan ON m_pekerjaan_id = pekerjaan_id
        LEFT JOIN m_pembayaran ON m_pembayaran_id = pembayaran_id";
        if ($search != ""){
            $sql .= " WHERE 0=1 ";
            $sql .= " OR cabang_nama like '%".$search."%' ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR monitor_mulai like '%".$search."%' "; 
            $sql .= " OR monitor_selesai like '%".$search."%' "; 
            $sql .= " OR monitor_tukang like '%".$search."%' "; 
            $sql .= " OR monitor_pekerja like '%".$search."%' ";
            $sql .= " OR pembayaran_ket like '%".$search."%' ";
            $sql .= " OR mandor_status like '%".$search."%' ";
        }
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function _mon_harsat($search){
		$sql = "SELECT 
            m_mandor.*,  m_proyek.*,  m_cabang.*,
            b.*, t_monitor. *,
            count(a.harsat_id) rowspan FROM t_monitor
        LEFT JOIN m_mandor ON m_mandor_id = mandor_id
        LEFT JOIN m_proyek ON m_proyek_id = proyek_id
        LEFT JOIN m_cabang ON m_cabang_id = cabang_id
        LEFT JOIN t_harsat a ON a.t_monitor_id = monitor_id
        LEFT JOIN t_harsat b ON b.t_monitor_id = monitor_id ";
        
        
        if ($search != ""){
            $sql .= " WHERE 0=1 ";
            $sql .= " OR cabang_nama like '%".$search."%' ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR mandor_status like '%".$search."%' ";
        }
        $sql .="
        GROUP BY monitor_id , b.harsat_id
        ORDER BY monitor_id";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function _re_proyek($data){
        extract($data);
		$sql = "SELECT * FROM t_monitor
        LEFT JOIN m_mandor ON m_mandor_id = mandor_id
        LEFT JOIN m_proyek ON m_proyek_id = proyek_id
        LEFT JOIN m_pekerjaan ON m_pekerjaan_id = pekerjaan_id
        LEFT JOIN m_pembayaran ON m_pembayaran_id = pembayaran_id
        WHERE 1=1 ";
        
        if (@$proyek_id != "" ){
            $sql .= " AND proyek_id = '$proyek_id'";
        }
        
        if (@$bulan != "" ){
            $tgl = date("Y-m-d",strtotime("01 ".$bulan));
            $sql .= " AND ((MONTH(monitor_mulai) = MONTH('$tgl')
                AND YEAR(monitor_mulai) = YEAR('$tgl') )
                OR (MONTH(monitor_selesai) = MONTH('$tgl')
                AND YEAR(monitor_selesai) = YEAR('$tgl') ) )";
        }
        
        
        if ($search != ""){
            $sql .= " AND ( 0=1 ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR monitor_mulai like '%".$search."%' "; 
            $sql .= " OR monitor_selesai like '%".$search."%' "; 
            $sql .= " OR monitor_tukang like '%".$search."%' "; 
            $sql .= " OR monitor_pekerja like '%".$search."%' ";
            $sql .= " OR pembayaran_ket like '%".$search."%' ";
            $sql .= " OR pekerjaan_ket like '%".$search."%' ";
            $sql .= " OR mandor_status like '%".$search."%' ) ";
        }
        
        $sql .= " ORDER BY proyek_id ";
        
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function _re_cabang($data){
        extract($data);
		$sql = "SELECT * FROM t_monitor
        LEFT JOIN m_mandor ON m_mandor_id = mandor_id
        LEFT JOIN m_proyek ON m_proyek_id = proyek_id
        LEFT JOIN m_cabang ON m_proyek.m_cabang_id = m_cabang.cabang_id
        LEFT JOIN m_pekerjaan ON m_pekerjaan_id = pekerjaan_id
        LEFT JOIN m_pembayaran ON m_pembayaran_id = pembayaran_id
        WHERE 1=1 ";
        
        if (@$cabang_id != "" ){
            $sql .= " AND m_cabang_id = '$cabang_id'";
        }
        
        if (@$bulan != "" ){
            $tgl = date("Y-m-d",strtotime("01 ".$bulan));
            $sql .= " AND ((MONTH(monitor_mulai) = MONTH('$tgl')
                AND YEAR(monitor_mulai) = YEAR('$tgl') )
                OR (MONTH(monitor_selesai) = MONTH('$tgl')
                AND YEAR(monitor_selesai) = YEAR('$tgl') ) )";
        }
        
        
        if ($search != ""){
            $sql .= " AND ( 0=1 ";
            $sql .= " OR cabang_nama like '%".$search."%' ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR monitor_mulai like '%".$search."%' "; 
            $sql .= " OR monitor_selesai like '%".$search."%' "; 
            $sql .= " OR monitor_tukang like '%".$search."%' "; 
            $sql .= " OR monitor_pekerja like '%".$search."%' ";
            $sql .= " OR pembayaran_ket like '%".$search."%' ";
            $sql .= " OR pekerjaan_ket like '%".$search."%' ";
            $sql .= " OR mandor_status like '%".$search."%' ) ";
        }
        
        
        $sql .= " ORDER BY cabang_id ";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function _re_dvo($data = array()){
        extract($data);
		$sql = "SELECT * FROM t_monitor
        LEFT JOIN m_mandor ON m_mandor_id = mandor_id
        LEFT JOIN m_proyek ON m_proyek_id = proyek_id
        LEFT JOIN m_pekerjaan ON m_pekerjaan_id = pekerjaan_id
        LEFT JOIN m_pembayaran ON m_pembayaran_id = pembayaran_id
        LEFT JOIN m_cabang ON m_cabang_id = cabang_id
        WHERE 1=1 
        ";
        
        if (@$bulan != "" ){
            $tgl = date("Y-m-d",strtotime("01 ".$bulan));
            $sql .= " AND ((MONTH(monitor_mulai) = MONTH('$tgl')
                AND YEAR(monitor_mulai) = YEAR('$tgl') )
                OR (MONTH(monitor_selesai) = MONTH('$tgl')
                AND YEAR(monitor_selesai) = YEAR('$tgl') ) )";
        }
        
        if ($search != ""){
            $sql .= " AND ( 0=1 ";
            $sql .= " OR cabang_nama like '%".$search."%' ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_alamat like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR monitor_mulai like '%".$search."%' "; 
            $sql .= " OR monitor_selesai like '%".$search."%' "; 
            $sql .= " OR monitor_tukang like '%".$search."%' "; 
            $sql .= " OR monitor_pekerja like '%".$search."%' ";
            $sql .= " OR pembayaran_ket like '%".$search."%' ";
            $sql .= " OR pekerjaan_ket like '%".$search."%' ";
            $sql .= " OR mandor_status like '%".$search."%' ) ";
        }
        $sql .= " ORDER BY m_cabang_id ";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
    function _re_harsat($data = array()){
        extract($data);
        $sql = "select * from t_harsat 
        left join t_monitor on t_monitor_id = monitor_id
        left join m_proyek on m_proyek_id = proyek_id
        left join m_mandor on m_mandor_id = mandor_id
        left join m_pekerjaan on m_pekerjaan_id = pekerjaan_id
        WHERE 1=1 ";
        
        if (@$pekerjaan_id != ""){
            $sql .= " AND pekerjaan_id in ( $pekerjaan_id)";
        }
        
        if ($search != ""){
            $sql .= " AND ( 0=1 ";
            $sql .= " OR harsat_ket like '%".$search."%' ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_alamat like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR monitor_mulai like '%".$search."%' "; 
            $sql .= " OR monitor_selesai like '%".$search."%' "; 
            $sql .= " OR monitor_tukang like '%".$search."%' "; 
            $sql .= " OR monitor_pekerja like '%".$search."%' ";
            $sql .= " OR pekerjaan_ket like '%".$search."%' ";
            $sql .= " OR mandor_status like '%".$search."%' ) ";
        }
        
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
        
    }
    
    function _map_mandor($data){
        extract($data);
        $sql = "call prepare_tablesplit";
        $this->db->query($sql);
        $sql = "SELECT mandor_id, mandor_kemampuan FROM m_mandor";
        $query = $this->db->query($sql);
        foreach($query->result()  as $result){
            $sql = "call split_string('".$result->mandor_kemampuan."',',',".$result->mandor_id.");";
            $this->db->query($sql);
        }
    
        $sql = "select * from ( SELECT * FROM m_proyek, splitResults) t
        LEFT JOIN m_mandor ON mandor_id = split_id
        LEFT JOIN m_pekerjaan ON pekerjaan_id = split_value
        LEFT JOIN m_cabang ON m_cabang_id = cabang_id 
        LEFT JOIN m_pendanaan ON m_pendanaan_id = pendanaan_id
        LEFT JOIN t_monitor ON m_pekerjaan_id = pekerjaan_id
				 and m_proyek_id = proyek_id
				 and m_mandor_id = mandor_id ";
                 
        if (@$pekerjaan_id != ""){
            $sql .= " WHERE pekerjaan_id in ( $pekerjaan_id)";
        }
           
        if ($search != ""){
            $sql .= " AND ( 0=1 ";
            $sql .= " OR harsat_ket like '%".$search."%' ";
            $sql .= " OR proyek_nama like '%".$search."%' ";
            $sql .= " OR proyek_kode like '%".$search."%' ";
            $sql .= " OR mandor_nama like '%".$search."%' ";
            $sql .= " OR mandor_kode like '%".$search."%' ";
            $sql .= " OR mandor_bu like '%".$search."%' ";
            $sql .= " OR mandor_alamat like '%".$search."%' ";
            $sql .= " OR mandor_npwp like '%".$search."%' ";
            $sql .= " OR mandor_telpon like '%".$search."%' ";
            $sql .= " OR mandor_fax like '%".$search."%' ";
            $sql .= " OR mandor_email like '%".$search."%' ";
            $sql .= " OR mandor_pengalaman like '%".$search."%' "; 
            $sql .= " OR mandor_wakil like '%".$search."%' ";
            $sql .= " OR mandor_tukang1 like '%".$search."%' ";
            $sql .= " OR mandor_pekerja like '%".$search."%' ";
            $sql .= " OR mandor_kota like '%".$search."%' "; 
            $sql .= " OR monitor_mulai like '%".$search."%' "; 
            $sql .= " OR monitor_selesai like '%".$search."%' "; 
            $sql .= " OR monitor_tukang like '%".$search."%' "; 
            $sql .= " OR monitor_pekerja like '%".$search."%' ";
            $sql .= " OR pekerjaan_ket like '%".$search."%' ";
            $sql .= " OR mandor_status like '%".$search."%' ) ";
        }
             
        $sql .= " ORDER BY proyek_id, mandor_id, pekerjaan_id";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
        
    }
    
    
    function _map_mandor_selesai($data){
        extract($data);
   /*     $sql = "call prepare_tablesplit";
        $this->db->query($sql);
        $sql = "SELECT mandor_id, mandor_kemampuan FROM m_mandor";
        $query = $this->db->query($sql);
        foreach($query->result()  as $result){
            $sql = "call split_string('".$result->mandor_kemampuan."',',',".$result->mandor_id.");";
            $this->db->query($sql);
        }
    */
        $sql = "SELECT monitor_id,
            m_proyek.proyek_nama, mandor_nama, pekerjaan_ket,
            t_monitor.m_proyek_id, m_mandor_id, monitor_selesai, m_pekerjaan_id,
            t_mapping.m_proyek_id to_proyek_id, t_mapping.mapping_tglmulai,
            to_proyek.proyek_nama to_proyek_nama,
            t_mapping.mapping_tglselesai FROM t_monitor
            LEFT JOIN m_proyek ON t_monitor.m_proyek_id = proyek_id
            LEFT JOIN m_mandor ON m_mandor_id = mandor_id
            LEFT JOIN m_pekerjaan ON m_pekerjaan_id = pekerjaan_id
            LEFT JOIN t_mapping ON monitor_id = t_monitor_id
            LEFT JOIN m_proyek to_proyek ON t_mapping.m_proyek_id = to_proyek.proyek_id
            WHERE monitor_selesai >= now()
            ORDER BY m_proyek.proyek_nama ,monitor_selesai ASC ";
        
        
       /*          
        if (@$pekerjaan_id != ""){
            $sql .= " WHERE pekerjaan_id in ( $pekerjaan_id)";
        }
                
        $sql .= " ORDER BY proyek_id, mandor_id, pekerjaan_id"; */
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
        
    }
    
}